package com.snailhd.common.db;

import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author hd
 * @description
 * @date 2019-08-16 16:37
 */

@Slf4j
public class SimpleMySQLClient {

    private final static int ONE_COLUME = 1;
    private final static int ONE_ROW = 1;

    private String driverClassName;
    private String url;
    private String username;
    private String password;


    public SimpleMySQLClient(String driverClassName, String url, String username, String password) {
        this.driverClassName = driverClassName;
        this.url = url;
        this.username = username;
        this.password = password;
    }

    /**
     * java与MySQL的连接
     *
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    private Connection getConnection() throws ClassNotFoundException, SQLException {
        Class.forName(driverClassName);  //加载数据库驱动
        return (Connection) DriverManager.getConnection(url, username, password); //创建连接
    }

    public <T> List<T> selectList(String sql, Object parameter, Class<T> clazz) throws ClassNotFoundException, SQLException {
        return executeQuery(sql, new DBParseExecuter<List<T>>() {
            @Override
            public List<T> parse(ResultSet rs) throws SQLException {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();

                DBResultParseInvoker invoker = new DBResultParseInvoker();
                boolean isSimpleResult = invoker.isSimpleResut(clazz);
                if (isSimpleResult && columnCount != ONE_COLUME) {
                    throw new SQLException("返回" + columnCount + "个参数，无法赋值给一个返回值");
                } else {
                    List<T> list = new ArrayList<>();
                    while (rs.next()) {
                        if (isSimpleResult) {
                            list.add(invoker.invoke(rs, clazz, metaData.getColumnName(ONE_COLUME)));
                        } else {
                            list.add(invoker.invokeObject(rs, clazz));
                        }
                    }
                    return list;
                }
            }
        });
    }

    public <T> T selectOne(String sql, Object parameter, Class<T> clazz) throws ClassNotFoundException, SQLException {

        return executeQuery(sql, new DBParseExecuter<T>() {
            @Override
            public T parse(ResultSet rs) throws SQLException {

                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                rs.last();
                int rowCount = rs.getRow();
                rs.first();

                DBResultParseInvoker invoker = new DBResultParseInvoker();
                if (invoker.isSimpleResut(clazz)) {
                    if (columnCount != ONE_COLUME) {
                        throw new SQLException("返回" + columnCount + "个参数，无法赋值给一个返回值");
                    } else if (rowCount > ONE_ROW) {
                        throw new SQLException("查询到" + rowCount + "行");
                    } else if (rowCount == ONE_ROW && columnCount == ONE_COLUME) {
                        return invoker.invoke(rs, clazz, metaData.getColumnName(ONE_COLUME));
                    } else {
                        return null;
                    }
                } else {
                    return invoker.invokeObject(rs, clazz);
                }
            }
        });
    }

    private <T> T executeQuery(String sql, DBParseExecuter<T> dbExecuter) throws SQLException, ClassNotFoundException {
        Connection conn = getConnection();
        Statement stmt = null;
        ResultSet rs = null;
        try {
            //访问数据库
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            printExecLog(sql, rs);
            return dbExecuter.parse(rs);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException sqlEx) {
                    log.error("关闭链接失败", sqlEx);
                } // ignore
                rs = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException sqlEx) {
                    log.error("关闭链接失败", sqlEx);
                } // ignore
                stmt = null;
            }
        }
    }


    public Integer update(String sql) throws SQLException, ClassNotFoundException {
        Connection conn = getConnection();
        Statement stmt = null;
        try {
            //访问数据库
            stmt = conn.createStatement();
            printExecLog(sql, null);
            return stmt.executeUpdate(sql);
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException sqlEx) {
                    log.error("关闭链接失败", sqlEx);
                } // ignore
                stmt = null;
            }
        }
    }


    public void printExecLog(String sql, ResultSet rs) throws SQLException {
        StringBuilder sb = new StringBuilder();
        sb.append("执行sql:").append(sql).append("\r\n")
                .append("入参：").append("").append("\r\n")
                .append("结果：");
        if(rs==null) { return; };
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        int rowCount = rs.getRow();
        for (int i = 1; i <= columnCount; i++) {
            sb.append(metaData.getColumnName(i)).append("\t");
        }

        sb.append("\r\n");
        while (rs.next()) {
            for (int i = 1; i <= columnCount; i++) {
                sb.append(rs.getObject(i)).append("\t");
            }
            sb.append("\r\n");
        }
        rs.beforeFirst();
        log.info(sb.toString());
    }
}

abstract class DBParseExecuter<T>{
    public abstract T parse(ResultSet rs) throws SQLException;
}

